/**
 * Copyright (c) 2008,中企动力华南研发中心
 * All rights reserved.
 */
package com.ce.pms.common.util;


import java.awt.Point;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.ce.util.DateUtil;
/**
 * 逻辑、功能相关描述:用于导出excel文件的公共类
 *
 * @author 于国岗  编写日期：2008-01-21
 */
public class PmsXLS {
	// 导出类型定义
	final static short EXPORT_TYPE_NUMERIC = 0;// 数值型

	final static short EXPORT_TYPE_STRING = 1;// 字符串型

	final static short EXPORT_TYPE_DATE = 2;// 日期型

	private HSSFWorkbook workbook;// 工作薄对象

	private List<PmsSheet> sheets;// 自定义的工作表集

	// 定义的内部sheet
	public class PmsSheet {
		final static boolean USE_SEQUENCE = true;// 使用序号

		final static boolean NO_SEQUENCE = false;// 不使用序号

		private boolean flagSequence = NO_SEQUENCE;// 默认是不使用序号

		private Point currentPos = new Point();// 当前工作位置，代表一个单元格的位置

		// private short sequenceNumber = 1;//序号的初始值

		private short colunms;// 导出数据所占用的列数

		private HSSFSheet sheet;// HSSF库的sheet对象，PmsSheet最终还是通过调用HSSFSheet对象来工作的

		PmsSheet(HSSFWorkbook workbook) {
			this.sheet = workbook.createSheet();
		}

		public short getColunms() {
			return colunms;
		}

		public void setColunms(short colunms) {
			this.colunms = colunms;
		}

		public Point getCurrentPos() {
			return currentPos;
		}

		public void setCurrentPos(Point currentPos) {
			this.currentPos = currentPos;
		}

		public boolean isFlagSequence() {
			return flagSequence;
		}

		public void setFlagSequence(boolean flagSequence) {
			this.flagSequence = flagSequence;
		}

		public HSSFSheet getSheet() {
			return sheet;
		}

		public void setSheet(HSSFSheet sheet) {
			this.sheet = sheet;
		}
	}

	// private ArrayList<String> titles=new ArrayList<String>();
	/**
     * Function:构造器，创建工作薄
     * @param sheetNumber 工作表的数目，至少为1
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public PmsXLS(int sheetNumber) {

		workbook = new HSSFWorkbook();
		sheets = new ArrayList<PmsSheet>(sheetNumber);

		for (int i = 0; i < sheetNumber; i++) {
			sheets.add(new PmsSheet(workbook));
		}
	}
	/**
     * Function:取得导出数据标题的样式（白底红字）
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private HSSFCellStyle getTitleStyle() {

		HSSFFont font2 = workbook.createFont();
		font2.setColor(HSSFFont.COLOR_RED);
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short) 12);
		HSSFCellStyle title = workbook.createCellStyle();
		title.setFont(font2);
		title.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居中
		title.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); // 垂直居中
		title.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

		return title;
	}
	/**
     * Function:取得导出数值数据的样式（灰底黑字）
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private HSSFCellStyle getNumberStyle() {

		HSSFCellStyle data = getExportDataStyle();
		data.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));

		return data;
	}

	// private HSSFCellStyle getDateStyle() {
	// HSSFCellStyle data = getExportDataStyle();
	// data.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
	// data.setFillForegroundColor(HSSFColor.WHITE.index); // 设置单元格显示颜色
	// return data;
	// }
	//
	// private HSSFCellStyle getBlankStyle() {
	// HSSFCellStyle data = getExportDataStyle();
	// data.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.0"));
	// data.setFillForegroundColor(HSSFColor.WHITE.index); // 设置单元格显示颜色
	// return data;
	// }

	@SuppressWarnings("static-access")
	/**
     * Function:取得导出数据的样式（灰底黑字）
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private HSSFCellStyle getExportDataStyle() {

		HSSFFont font2 = workbook.createFont();
		// font2.setColor(HSSFFont.COLOR_RED);
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short) 12);
		HSSFCellStyle data = workbook.createCellStyle();
		data.setFont(font2);
		data.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居中
		data.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); // 垂直居中
		data.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

		data.setBorderBottom(data.BORDER_THIN);
		data.setBorderLeft(data.BORDER_THIN);
		data.setBorderRight(data.BORDER_THIN);
		data.setBorderTop(data.BORDER_THIN);

		data.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 样式的填充类型。
		// 有多种式样如:
		// HSSFCellStyle.BIG_SPOTS
		// HSSFCellStyle.FINE_DOTS
		// HSSFCellStyle.SPARSE_DOTS等
		data.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中对齐
		// data.setFillBackgroundColor(HSSFColor.GREEN.index); //设定单元个背景颜色
		data.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置单元格显示颜色

		return data;
	}

	/**
     * Function:设置工作表名
     * @param name 工作表的名称
     * @param index 工作表的序号(以零为基)
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public void setSheetName(String name, int index) {

		workbook.setSheetName(index, name, HSSFWorkbook.ENCODING_UTF_16);

	}

    /**
     * Function:在给定位置创建标题
	 * @param titles 代表标题的字符串数组
	 * @param point 该参数以二维坐标点表示位置。如(0,0)表示左上角A1,(0,1)表示B1...等
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public void createTitle(String[] titles, Point point, PmsSheet sheet) {

		// 取得行
		HSSFRow row = sheet.getSheet().createRow((short) point.getY());
		// 从哪一列开始
		short beginColumn = (short) point.getX();
		@SuppressWarnings("unused")
		HSSFCell cell;
		if (sheet.isFlagSequence() == PmsSheet.USE_SEQUENCE) {
			cell = createStringCell(row, beginColumn++, getTitleStyle(), "序号");
			sheet.setColunms((short) (titles.length + 1));
		} else {
			sheet.setColunms((short) titles.length);
		}
		for (String title : titles) {
			createStringCell(row, beginColumn++, getTitleStyle(), title);
		}
		// 更新当前位置
		sheet.getCurrentPos().move((int) point.getX(), (int) point.getY() + 1);
	}

	 /**
     * Function:写字符串数据
	 * @param row 要写入数据的工作表row对象
	 * @param index 对应row对象的写入位置.
	 * @param style 样式
	 * @param value 值
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private HSSFCell createStringCell(HSSFRow row, short index,
			HSSFCellStyle style, String value) {

		HSSFCell cell = null;
		cell = row.createCell(index);
		// 定义单元格为字符串类型
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(value);

		return cell;
	}

	 /**
     * Function:写数值数据
	 * @param row 要写入数据的工作表row对象
	 * @param index 对应row对象的写入位置.
	 * @param style 样式
	 * @param value 值
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private HSSFCell createNumberCell(HSSFRow row, short index,
			                              HSSFCellStyle style, Number value) {

		HSSFCell cell = null;
		cell = row.createCell(index);
		// 定义单元格为字符串类型
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.0"));
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(value.doubleValue());
		System.out.println("ddd");

		return cell;
	}

    /**
     * Function:把要导出的数据写入工作表对象
	 * @param sheet 要写入数据的工作表对象
	 * @param datas 要写入的数据，以List表示的VO集合
	 * @param properties 以String数组形式表示的写入顺序
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public void setDatas(PmsSheet sheet, List datas, String[] properties) {

		for (Object data : datas) {
			// 取得行
			HSSFRow row = sheet.getSheet().createRow(
					(short) sheet.getCurrentPos().getY());
			short index = (short) sheet.getCurrentPos().getX();
			// 使用序号
			// if (flagSequence == USE_SEQUENCE) {
			// setData(row, index++, String.valueOf(sequenceNumber++));
			// }

			//如果没有传入属性次序，则按默认的反射次序。
			if (properties == null) {

				Field[] fields = data.getClass().getDeclaredFields();
				properties = new String[fields.length];
				int i = 0;
				for (Field field : fields) {
					properties[i++] = field.getName();
				}
			}

			//依序调用属性的get方法取得值并写入sheet
			for (String s : properties) {
				s = "get" + Character.toUpperCase(s.charAt(0)) + s.substring(1);
				Method m;
				try {
					m = data.getClass().getMethod(s, (Class[]) null);
					Object result = m.invoke(data, new Object[] {});
					//写入数据
					setData(row, index++, result);
				} catch (SecurityException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}

			// 下一行
			sheet.getCurrentPos().y++;
		}

	}

	@SuppressWarnings("unused")
	/**
     * Function:设置空白单元格
	 * @param row 要写入数据的工作表row对象
	 * @param index 对应row对象的写入位置.
	 * @param style 样式
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private void setBlank(final HSSFRow row, final short index,
			final HSSFCellStyle style) {

		HSSFCell cell = null;
		cell = row.createCell(index);
		// 定义单元格为字符串类型
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("");

	}

//	protected void setDate(HSSFRow row, short index, HSSFCellStyle style) {
//		HSSFCell cell = null;
//		cell = row.createCell(index);
//		// 定义单元格为字符串类型
//		cell.setCellStyle(style);
//		// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//		cell.setCellValue("");
//		// cell.setCellValue(Calendar.getInstance().getTime());
//	}

	/**
     * Function:根据java类型导出到对应的单元格数据类型
	 * @param row 要写入数据的工作表row对象
	 * @param index 对应row对象的写入位置.
	 * @param value 数据项
     * @return
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private void setData(HSSFRow row, short index, Object value) {

		switch (checkType(value)) {
		case EXPORT_TYPE_NUMERIC:
			//数值型
			createNumberCell(row, index, getNumberStyle(), (Number) value);
			break;
		case EXPORT_TYPE_DATE:
			//日期型
			break;
		case EXPORT_TYPE_STRING:
			//字符串型
			createStringCell(row, index, getExportDataStyle(), (String) value);
			break;
		}

	}
	/**
     * Function:根据o的类型分类
	 * @param o 数据项
     * @return EXPORT_TYPE_STRING   字符串型
     * 		   EXPORT_TYPE_NUMERIC  数值型
     *         EXPORT_TYPE_DATE     日期型
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	private short checkType(Object o) {

		short retValue = EXPORT_TYPE_STRING;

		if (o instanceof Number) {
			retValue = EXPORT_TYPE_NUMERIC;
		} else if (o instanceof Date) {
			retValue = EXPORT_TYPE_DATE;
		}

		return retValue;
	}

	/**
     * Function:写注脚
	 * @param sheet 要写入数据的工作表
	 * @param operator 导出人
	 * @param date 导出日期
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public void setFoot(PmsSheet sheet, String operator, Date date) {

		HSSFRow row = sheet.getSheet().createRow(
				(short) sheet.getCurrentPos().getY());
		short index = (short) sheet.getCurrentPos().getX();

		if (sheet.getColunms() > 4) {
			index = (short) (index + sheet.getColunms() - 4);
		}

		setData(row, index++, "导出人:");
		setData(row, index++, operator);
		setData(row, index++, "导出日期:");
		setData(row, index, DateUtil.convertDateToStr(date, "yyyy-MM-dd"));

	}

	/**
     * Function: 将导出的数据写入到一个文件中
	 * @param fileName 文件名
	 * @throws IOException
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public void write(String fileName) throws IOException {

		FileOutputStream fos = new FileOutputStream(fileName);
		workbook.write(fos);
		fos.flush();
		fos.close();

	}

	/**
     * Function: 将导出的数据写入到一个流
	 * @param fileName 流对象
	 * @throws IOException
     * Create author:于国岗
     * Create on:2008-01-21
     * Edit author:
     * Edit on:
     * Why:
     */
	public void write(OutputStream stream) {

		try {
			workbook.write(stream);
		} catch (final IOException e) {
			throw new RuntimeException(e);
		}
	}

	public List<PmsSheet> getSheets() {
		return sheets;
	}

	public void setSheets(List<PmsSheet> sheets) {
		this.sheets = sheets;
	}
}
